<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="java.util.Map,java.util.*"%>
<%@page import="java.util.List"%>
<%@ include file="conndb.jsp"%>
<%!

/**帮助方法获取集合**/
private List<Map<String,Object>> parseList(ResultSet rs){
	List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
	try{
		while(rs.next()){
			Map<String,Object> m = parseMap(rs);
			if(null!=m)
				list.add(m);
		}
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}
    return null;
}

/**帮助方法获取Map形式的对象**/
private Map<String,Object> parseMap(ResultSet rs){
	Map<String,Object> m = new HashMap<String,Object>();
	try{
		ResultSetMetaData rsmd = rs.getMetaData();
		int cc = rsmd.getColumnCount();
		for(int j=0;j<cc;j++){
			if(null!=rsmd.getColumnName(j+1)){
				if(rsmd.getColumnType(j+1)==2005){
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getString(rsmd.getColumnName(j+1)));
				}else if (rsmd.getColumnType(j+1)==91){
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getTimestamp(rsmd.getColumnName(j+1)));
				}else{
					m.put(rsmd.getColumnName(j+1).toLowerCase(),rs.getObject(rsmd.getColumnName(j+1)));
				}
			}
		}
		return m;
	}catch(Exception e){
		e.printStackTrace();
	}
	return null;	
}

/*#########################以上为私有方法###################################*/

public Map<String,Object> getObject(String sql){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
			
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Object> getObject(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public Map<String,Object> getObject(String sql,List<Object> params) throws Exception{
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
// 	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		if(rs.next()){
			Map<String,Object> m = parseMap(rs);
			return m;
		}
// 	}catch(Exception e){
// 		e.printStackTrace();
// 	}finally{
// 		closeConn(conn,pst,rs);
// 	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql,Object[] params) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForList(String sql,List<Object> params) {
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

/**
 * 分页查询
 * @param sql sql语句
 * @param params Object[]参数
 * @param currentPage 当前页数
 * @param pageSize 每页条数
**/
public List<Map<String,Object>> queryForListPage(String sql,Object[] params,int currentPage,int pageSize){
	if(pageSize==0){
		pageSize = 10;
	}
	String sql1 = "select tt3.* from (select rownum as r,tt2.* from (";
	String sql2 = ") tt2 where rownum<= ?) tt3 where tt3.r > ? ";
	if(currentPage<=0){
		currentPage = 1;
	}
	
	int start = (currentPage-1) * pageSize;
	int end = (currentPage-1) * pageSize +pageSize;
	
	String querySql = sql1 + sql + sql2;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(querySql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
			pst.setObject(params.length+1,end);
			pst.setObject(params.length+2,start);
		}else{
			pst.setObject(1,end);
			pst.setObject(2,start);
		}
		
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

public List<Map<String,Object>> queryForListPage(String sql,List<Object> params,int currentPage,int pageSize){
	if(pageSize==0){
		pageSize = 10;
	}
	String sql1 = "select tt3.* from (select rownum as r,tt2.* from (";
	String sql2 = ") tt2 where rownum<= ?) tt3 where tt3.r > ? ";
	if(currentPage<=0){
		currentPage = 1;
	}
	
	int start = (currentPage-1) * pageSize;
	int end = (currentPage-1) * pageSize +pageSize;
	
	String querySql = sql1 + sql + sql2;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(querySql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
			pst.setObject(params.size()+1,end);
			pst.setObject(params.size()+2,start);
		}else{
			pst.setObject(1,end);
			pst.setObject(2,start);
		}
		
		rs=pst.executeQuery();
		//获取集合列表
		List<Map<String,Object>> list = parseList(rs);
		return list;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return null;
}

/**
*分页用于查询总记录数
*@param 与分页查询sql一致
*@param params 与分页查询参数一致
*/
public int queryForPageCount(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		sql = "select count(*) from ("+sql+")";
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}
/**
*分页用于查询总记录数
*@param 与分页查询sql一致
*@param params 与分页查询参数一致
*/
public int queryForPageCount(String sql,List<Object> params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		sql = "select count(*) from ("+sql+")";
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

/***
*	查询总记录数
* @param sql查询数量的语句
*/
public int queryCount(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		rs=pst.executeQuery();
		if(rs.next()){
			int count = rs.getInt(1);
			return count;
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

//更新语句
public int executeUpdate(String sql,List<Object> params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.size();i++){
				pst.setObject(i+1,params.get(i));
			}
		}
		int count = pst.executeUpdate();
		return count;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

//更新语句
public int executeUpdate(String sql,Object[] params){
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try{
		conn=getConnection();
		pst=conn.prepareStatement(sql);
		if(null!=params){
			for(int i=0; i<params.length;i++){
				pst.setObject(i+1,params[i]);
			}
		}
		int count = pst.executeUpdate();
		return count;
	}catch(Exception e){
		e.printStackTrace();
	}finally{
		closeConn(conn,pst,rs);
	}
    return 0;
}

//字符串方法zhangfang
public String getMyString(Object str){
	if(str==null)
		str = "";
	String str1 = String.valueOf(str);
	return str1;
}

/**
 * 批量更新
 * 
 * @param sql
 * @param paramsList
 */
public boolean executeUpdateBatch(String sql, List<List<Object>> paramsList) {
    Connection conn = null;
    PreparedStatement pst = null;
	ResultSet rs = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        PreparedStatement prest = conn
                .prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
        for (int i = 0; i < paramsList.size(); i++) {
            List<Object> params = paramsList.get(i);
            for (int j = 0; j < params.size(); j++) {
                prest.setObject(j + 1, params.get(j));
            }
            prest.addBatch();
        }
        prest.executeBatch();
        conn.commit();
        return true;
    } catch (SQLException ex) {
        ex.printStackTrace();
        return false;
    } finally {
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConn(conn,pst,rs);
    }
}

/**
 * 事务处理
 * 
 * */
@SuppressWarnings("unchecked")
public boolean transaction(List<Map<String,Object>> dataBeans){
    Connection conn = null;
    PreparedStatement pst = null;
	ResultSet rs = null;
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        for(int i =0 ;i<dataBeans.size();i++){
            Map<String,Object> dataBean = dataBeans.get(i);
            String sql = (String)dataBean.get("sql");
            List<Object> params = (List<Object>)dataBean.get("params");
            pst = conn.prepareStatement(sql);
            if (null != params) {
                for (int j = 0; j < params.size(); j++) {
                    pst.setObject(j + 1, params.get(j));
                }
            }
            pst.executeUpdate();
        }
        conn.commit();
        return true;
    } catch (SQLException ex) {
        ex.printStackTrace();
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    } finally {
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        closeConn(conn,pst,rs);
    }
}
//StringToDate

%>